﻿
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace Model
{
   public class Member_profileModel
   {

       private DBUtil DB;
       public string strConn { get; set; }
       public int user_id { get; set; }
       public int user_type_id { get; set; }
       public string username { get; set; }
       public string password { get; set; }
       public string fname_th { get; set; }
       public string lname_th { get; set; }
       public string fname_en { get; set; }
       public string lname_en { get; set; }
       public string id_card { get; set; }
       public string birth_date { get; set; }
       public string face_image { get; set; }
       public string gender { get; set; }
       public string telephone { get; set; }
       public string mobile { get; set; }
       public string role { get; set; }
       public string lang { get; set; }
       public string create_date { get; set; }
       public int create_by { get; set; }
       public string update_date { get; set; }
       public int update_by { get; set; }
       public int site_id { get; set; }

     public Member_profileModel() {
          this.DB = new DBUtil();
          DB.strConn = strConn;
     }

     public DataTable getById(string id)
     {

         DataTable dt = new DataTable();
         this.DB = new DBUtil();
         DB.strConn = strConn;

         string sql = "SELECT * FROM tcdc_mst_user" +
                      " WHERE user_id = '" + id + "'";

         dt = DB.ExecuteReader(sql);

         return dt;

     }
    
     public DataTable getByObject(Member_profileModel obj)
     {

         DataTable dt = new DataTable();
         this.DB = new DBUtil();
         DB.strConn = strConn;

         string sql = "SELECT * FROM tcdc_mst_user " +
                      " WHERE 1 = 1 ";

         if (obj.user_id != null &&
            obj.user_id.ToString() != null &&
            obj.user_id.ToString() != "0")
         {
             sql += "AND user_id LIKE '%" + obj.user_id + "%' ";
         }
         if (obj.user_type_id != null &&
            obj.user_type_id.ToString() != null &&
            obj.user_type_id.ToString() != "0")
         {
             sql += "AND user_type_id LIKE '%" + obj.user_type_id + "%' ";
         }
         if (obj.username != null &&
            obj.username.ToString() != null &&
            obj.username.ToString() != "0")
         {
             sql += "AND username LIKE '%" + obj.username + "%' ";
         }
         if (obj.password != null &&
            obj.password.ToString() != null &&
            obj.password.ToString() != "0")
         {
             sql += "AND password LIKE '%" + obj.password + "%' ";
         }
         if (obj.fname_th != null &&
            obj.fname_th.ToString() != null &&
            obj.fname_th.ToString() != "0")
         {
             sql += "AND fname_th LIKE '%" + obj.fname_th + "%' ";
         }
         if (obj.lname_th != null &&
            obj.lname_th.ToString() != null &&
            obj.lname_th.ToString() != "0")
         {
             sql += "AND lname_th LIKE '%" + obj.lname_th + "%' ";
         }
         if (obj.fname_en != null &&
            obj.fname_en.ToString() != null &&
            obj.fname_en.ToString() != "0")
         {
             sql += "AND fname_en LIKE '%" + obj.fname_en + "%' ";
         }
         if (obj.lname_en != null &&
            obj.lname_en.ToString() != null &&
            obj.lname_en.ToString() != "0")
         {
             sql += "AND lname_en LIKE '%" + obj.lname_en + "%' ";
         }
         if (obj.id_card != null &&
            obj.id_card.ToString() != null &&
            obj.id_card.ToString() != "0")
         {
             sql += "AND id_card LIKE '%" + obj.id_card + "%' ";
         }
         if (obj.birth_date != null &&
            obj.birth_date.ToString() != null &&
            obj.birth_date.ToString() != "0")
         {
             sql += "AND birth_date LIKE '%" + obj.birth_date + "%' ";
         }
         if (obj.face_image != null &&
            obj.face_image.ToString() != null &&
            obj.face_image.ToString() != "0")
         {
             sql += "AND face_image LIKE '%" + obj.face_image + "%' ";
         }
         if (obj.gender != null &&
            obj.gender.ToString() != null &&
            obj.gender.ToString() != "0")
         {
             sql += "AND gender LIKE '%" + obj.gender + "%' ";
         }
         if (obj.telephone != null &&
            obj.telephone.ToString() != null &&
            obj.telephone.ToString() != "0")
         {
             sql += "AND telephone LIKE '%" + obj.telephone + "%' ";
         }
         if (obj.mobile != null &&
            obj.mobile.ToString() != null &&
            obj.mobile.ToString() != "0")
         {
             sql += "AND mobile LIKE '%" + obj.mobile + "%' ";
         }
         if (obj.role != null &&
            obj.role.ToString() != null &&
            obj.role.ToString() != "0")
         {
             sql += "AND role LIKE '%" + obj.role + "%' ";
         }
         if (obj.lang != null &&
            obj.lang.ToString() != null &&
            obj.lang.ToString() != "0")
         {
             sql += "AND lang LIKE '%" + obj.lang + "%' ";
         }
         if (obj.create_date != null &&
            obj.create_date.ToString() != null &&
            obj.create_date.ToString() != "0")
         {
             sql += "AND create_date LIKE '%" + obj.create_date + "%' ";
         }
         if (obj.create_by != null &&
            obj.create_by.ToString() != null &&
            obj.create_by.ToString() != "0")
         {
             sql += "AND create_by LIKE '%" + obj.create_by + "%' ";
         }
         if (obj.update_date != null &&
            obj.update_date.ToString() != null &&
            obj.update_date.ToString() != "0")
         {
             sql += "AND update_date LIKE '%" + obj.update_date + "%' ";
         }
         if (obj.update_by != null &&
            obj.update_by.ToString() != null &&
            obj.update_by.ToString() != "0")
         {
             sql += "AND update_by LIKE '%" + obj.update_by + "%' ";
         }
         if (obj.site_id != null &&
            obj.site_id.ToString() != null &&
            obj.site_id.ToString() != "0")
         {
             sql += "AND site_id LIKE '%" + obj.site_id + "%' ";
         }
         dt = DB.ExecuteReader(sql);

         return dt;

     }

     public DataTable getCampaign_Group()
     {

         DataTable dt = new DataTable();
         this.DB = new DBUtil();
         DB.strConn = strConn;

         string sql = "";


         sql = "select  c.campaign_id,CONVERT(varchar,c.campaign_group_id)+'. '+ campaign_group_name_th as campaign_group ,campaign_name_th,campaign_group_name_th+' '+campaign_name_th as campaign_name "+
                "from tcdc_mst_campaign as c "+
                "inner join tcdc_mst_campaign_group g on c.campaign_group_id =g.campaign_group_id "+
                "inner join tcdc_mst_campaign_name as n on c.campaign_name_id=n.campaign_name_id "+
                "order by g.campaign_group_id,n.campaign_name_id ";
         
 
         dt = DB.ExecuteReader(sql);

         return dt;

     }

     public DataTable getCampaign_Fee(string campaign_id)
     {

         DataTable dt = new DataTable();
         this.DB = new DBUtil();
         DB.strConn = strConn;

         string sql = "";

         sql = "select campaign_fee_id,c.campaign_id,convert(varchar,campaign_year)+' ปี '+ case campaign_month when null then '' else convert(varchar,campaign_month) +' เดือน'  end as campaign_month  ,campaign_price from tcdc_mst_campaign_fee f " +
                "inner join tcdc_mst_campaign c on f.campaign_id=c.campaign_id where c.campaign_id=" + campaign_id;

         dt = DB.ExecuteReader(sql);

         return dt;

     }
    
     
     
}
}
